package com.ck.core.util;

import com.ck.core.export.annotation.Cell;
import com.ck.core.export.entity.Entity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.TempFile;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.imageio.ImageIO;
import javax.xml.parsers.DocumentBuilderFactory;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

/**
 * 导出Excel工具类 支持导出xlsx（2007）
 */
@Component
@Configuration
public class ExportUtil {

    private static ExportUtil tool = new ExportUtil();
    public static int PRECISION = 3;
    private static String DEFAULT_COLUMN_WIDTH = "3200";
    //每個sheet 的row 數量
    private static final int EXCEL_ROW_MAX_SIZE = 50000;

    /**
     * 桶名称
     */
    @Value("${huawei.oss.bucket.name:''}")
    private String HUAWEI_OSS_BUCKET_NAME;
    /**
     * 域名
     */
    @Value("${huawei.oss.endpoint:''}")
    private String HUAWEI_OSS_ENDPOINT;

    private String huaweiBaseUrl = HUAWEI_OSS_BUCKET_NAME + HUAWEI_OSS_ENDPOINT;

    /**
     * 创建标题行
     *
     * @param workbook HSSFWorkbook
     * @param row      HSSFRow
     * @param titles   List<Entity>
     * @param sheet    HSSFSheet
     */
    private void createTitileRow(XSSFWorkbook workbook, XSSFRow row, String[] titles, HSSFSheet sheet) {
        int length = titles.length;
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setColor((short) 12);
        font.setBoldweight((short) 14);
        style.setFont(font);
        style.setFillBackgroundColor((short) 13);
        for (int i = 0; i < length; ++i) {
            sheet.setColumnWidth(i, 2560);
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
    }

    /**
     * 创建标题行
     *
     * @param workbook   HSSFWorkbook
     * @param row        HSSFRow
     * @param entityList List<Entity>
     * @param sheet      HSSFSheet
     */
    private void createTitleRow(SXSSFWorkbook workbook, SXSSFRow row, List<Entity> entityList, SXSSFSheet sheet) {
        CellStyle style = createHeadStyle(workbook);
//        HSSFFont font = workbook.createFont();
        //font.setColor((short)12);
        //font.setBoldweight((short)700);
        //style.setFont(font);
        //style.setFillBackgroundColor((short)13);
        int i = 0;
        for (Iterator localIterator = entityList.iterator(); localIterator.hasNext(); ) {
            Entity e = (Entity) localIterator.next();
            String columnWidth = e.getWidth();
            sheet.setColumnWidth(i, Integer.valueOf(columnWidth));
            SXSSFCell cell = row.createCell(i);
            cell.setCellValue(e.getText());
            cell.setCellStyle(style);
            ++i;
        }
    }

    /**
     * 创建标题样式
     *
     * @param workbook HSSFWorkbook
     * @return HSSFCellStyle
     */
    private CellStyle createHeadStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment((short) 2);
        style.setVerticalAlignment((short) 1);
        style.setFillForegroundColor((short) 55);
        style.setFillPattern((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setBorderTop((short) 1);
        style.setWrapText(true);
        return style;
    }

    /**
     * 创建单元格样式
     *
     * @param workbook HSSFWorkbook
     * @return HSSFCellStyle
     */
    private HSSFCellStyle createCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment((short) 2);
        style.setVerticalAlignment((short) 1);
        style.setFillForegroundColor((short) 9);
        HSSFFont font = workbook.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        style.setWrapText(true);
        return style;
    }

    /**
     * 解析xml文件
     *
     * @param clazz 封装导出到excel的POJO类
     * @return List<Entity>
     * @throws Exception
     */
    private List<Entity> parseXML(Class<? extends Object> clazz) throws Exception {
        List list = new ArrayList();
        String clazzSimpleName = clazz.getSimpleName();
        //从classpath目录下加载xml文件，xml文件配置了具体要导出的列
        ClassPathResource classPathResource = new ClassPathResource("export/" + clazzSimpleName + ".ete.xml");
        InputStream inputStream = classPathResource.getInputStream();
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(inputStream);
        NodeList nodeList = doc.getElementsByTagName("property");
        for (int i = 0; i < nodeList.getLength(); ++i) {
            Element element = (Element) nodeList.item(i);
            Entity e = new Entity();
            e.setNeme(element.getAttribute("name"));
            e.setText(element.getAttribute("text"));
            e.setType(element.getAttribute("type"));
            String width = element.getAttribute("width");
            if (StringUtils.isBlank(width)) {
                //设置column默认宽度
                e.setWidth(DEFAULT_COLUMN_WIDTH);
            } else {
                e.setWidth(width);
            }
            list.add(e);
        }
        return list;
    }

    /**
     * 解析注解
     *
     * @param clazz Class<? extends Object>
     * @return List<Entity>
     * @throws ClassNotFoundException
     */
    private List<Entity> parseAnnotation(Class<? extends Object> clazz) throws ClassNotFoundException {
        List entities = new ArrayList();
        Field[] fields = clazz.getDeclaredFields();

        Field[] arrayOfField1 = fields;
        int j = fields.length;
        for (int i = 0; i < j; ++i) {
            Field field = arrayOfField1[i];
            Entity e = new Entity();
            boolean hasAnnotation = field.isAnnotationPresent(Cell.class);
            if (hasAnnotation) {
                Cell annotation = (Cell) field.getAnnotation(Cell.class);
                e.setText(annotation.title());
                e.setType(field.getType().getName());
                e.setNeme(field.getName());
                entities.add(e);
            }
        }
        return entities;
    }

    /**
     * 导出数据到excel -多个sheet组合
     *
     * @param dataMap  map的key即单个sheet的名字
     * @return HSSFWorkbook
     * @throws Exception
     */
    public SXSSFWorkbook exportMultipleSheetToExcel(Map<String,List<? extends Object>> dataMap) throws Exception {
        if (CollectionUtils.isEmpty(dataMap)){
            return null;
        }
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File dir = new File(path.getAbsolutePath(), "poitmp/");
        if (!dir.exists()) {
            dir.mkdirs();
            TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
        }
        //创建HSSFWorkbook
        SXSSFWorkbook workbook = new SXSSFWorkbook();

        for (Map.Entry<String,List<? extends Object>> entry: dataMap.entrySet()) {
            MathContext mathContext = new MathContext(PRECISION, RoundingMode.HALF_UP);
            List<?> list = entry.getValue();
            if (list == null || list.size() == 0) {
                //提示暂无内容
                SXSSFSheet sheet = workbook.createSheet(entry.getKey());
                sheet.createRow(0);
                // 设置字体
                Font headfont = workbook.createFont();
                headfont.setFontName("黑体");
                // 字体大小
                headfont.setFontHeightInPoints((short) 22);
                // 加粗
                headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                CellStyle headstyle = workbook.createCellStyle();
                headstyle.setFont(headfont);
                // 左右居中
                headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                // 上下居中
                headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                // 创建第一行
                SXSSFRow row0 = sheet.createRow(0);
                // 设置行高
                row0.setHeight((short) 900);
                // 创建第一列
                SXSSFCell cell0 = row0.createCell(0);
                cell0.setCellValue(new XSSFRichTextString("暂无数据！！！"));
                cell0.setCellStyle(headstyle);
                /**
                 * 合并单元格
                 *    第一个参数：第一个单元格的行数（从0开始）
                 *    第二个参数：第二个单元格的行数（从0开始）
                 *    第三个参数：第一个单元格的列数（从0开始）
                 *    第四个参数：第二个单元格的列数（从0开始）
                 */
                CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
                sheet.addMergedRegion(range);
                return workbook;
            }
            Class clazz = list.get(0).getClass();
            //解析xml文件，解析出导出列
            List entities = tool.parseXML(clazz);
            if ((entities == null) || (entities.size() == 0)) {
                entities = tool.parseAnnotation(clazz);
            }
            int index = 0;
            SXSSFSheet sheet = null;
            Drawing patriarch = null;
            for (int i = 0; i < list.size(); ++i) {
                //当条数大于阈值时切换sheet
                if ((i % EXCEL_ROW_MAX_SIZE) == 0) {
                    //创建HSSFSheet
                    sheet = workbook.createSheet(entry.getKey()+index);
                    //创建HSSFRow
                    SXSSFRow titleRow = sheet.createRow(0);
                    titleRow.setHeightInPoints(25.0F);
                    //创建标题行
                    tool.createTitleRow(workbook, titleRow, entities, sheet);
                    // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                    patriarch = sheet.createDrawingPatriarch();
                    index ++;
                }
                //切换sheet后需要自动计算数据在当前sheet的rowPosition
                int rowNum = (i + 1) - ((index-1) * EXCEL_ROW_MAX_SIZE);
                //创建行
                SXSSFRow row = sheet.createRow(rowNum);
                row.setHeightInPoints(23.0F);
                int colNum = 0;
                sheetHandler(workbook, mathContext, list, entities, patriarch, i, rowNum, row, colNum);
            }
        }
        return workbook;

    }

    private void sheetHandler(SXSSFWorkbook workbook, MathContext mathContext, List<?> list, List entities, Drawing patriarch, int i, int rowNum, SXSSFRow row, int colNum) throws Exception {
        BigDecimal bigDecimal;
        for (Iterator localIterator = entities.iterator(); localIterator.hasNext(); ) {
            Entity entity = (Entity) localIterator.next();
            String type = entity.getType();
            String name = entity.getNeme();
            //创建列
            SXSSFCell cell = row.createCell(colNum);
            Object obj = null;
            if (("boolean".equals(type)) || ("java.lang.Boolean".equals(type))) {
                obj = MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnIsBooleanMethodName(name));
            } else {
                obj = MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnGetMethodName(name));
            }
            //设置数据到列
            if (obj != null) {
                if (type.endsWith("String")) {
                    cell.setCellValue((String) obj);
                } else if (("int".equalsIgnoreCase(type)) || ("java.lang.Integer".equals(type)) || ("Integer".equals(type))) {
                    cell.setCellValue((Integer) obj);
                } else if (("double".equalsIgnoreCase(type)) || ("java.lang.Double".equals(type))) {
                    bigDecimal = new BigDecimal((Double) obj, mathContext);
                    cell.setCellValue(bigDecimal.doubleValue());
                } else if (("boolean".equalsIgnoreCase(type)) || ("java.lang.Boolean".equals(type))) {
                    cell.setCellValue((Boolean) MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnIsBooleanMethodName(name)));
                } else if (("float".equalsIgnoreCase(type)) || ("java.lang.Float".equals(type))) {
                    bigDecimal = new BigDecimal((Float) obj, mathContext);
                    cell.setCellValue(bigDecimal.doubleValue());
                } else if (("java.util.Date".equals(type)) || (type.endsWith("Date"))) {
                    Date date = (Date) obj;
                    cell.setCellValue(DateUtils.dateToString(date,DateUtils.PATTEN_YMD_HMS));
                } else if ("java.util.Calendar".equals(type)) {
                    cell.setCellValue((Calendar) obj);
                } else if (("char".equalsIgnoreCase(type)) || ("java.lang.Character".equals(type))) {
                    cell.setCellValue(obj.toString());
                } else if (("long".equalsIgnoreCase(type)) || ("java.lang.Long".equals(type))) {
                    cell.setCellValue((Long) obj);
                } else if (("short".equalsIgnoreCase(type)) || ("java.lang.Short".equals(type))) {
                    cell.setCellValue((Short) obj);
                } else if ("java.math.BigDecimal".equals(type)) {
                    bigDecimal = (BigDecimal) obj;
                    bigDecimal = new BigDecimal(bigDecimal.doubleValue(), mathContext);
                    cell.setCellValue(bigDecimal.doubleValue());
                } else {
                    throw new Exception("data type error!");
                }
            }
            ++colNum;
        }
    }

    /**
     * 导出数据到excel
     *
     * @param list      List<? extends Object>
     * @param sheetName sheet名称
     * @return HSSFWorkbook
     * @throws Exception
     */
    public SXSSFWorkbook exportToExcel(List<? extends Object> list, String sheetName) throws Exception {
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File dir = new File(path.getAbsolutePath(), "poitmp/");
        if (!dir.exists()) {
            dir.mkdirs();
            TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
        }
        if (list == null || list.size() == 0) {
            //提示暂无内容
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            SXSSFSheet sheet = workbook.createSheet(sheetName);
            SXSSFRow row = sheet.createRow(0);
            // 设置字体
            Font headfont = workbook.createFont();
            headfont.setFontName("黑体");
            // 字体大小
            headfont.setFontHeightInPoints((short) 22);
            // 加粗
            headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            CellStyle headstyle = workbook.createCellStyle();
            headstyle.setFont(headfont);
            // 左右居中
            headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 上下居中
            headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            // 创建第一行
            SXSSFRow row0 = sheet.createRow(0);
            // 设置行高
            row0.setHeight((short) 900);
            // 创建第一列
            SXSSFCell cell0 = row0.createCell(0);
            cell0.setCellValue(new XSSFRichTextString("暂无数据！！！"));
            cell0.setCellStyle(headstyle);
            /**
             * 合并单元格
             *    第一个参数：第一个单元格的行数（从0开始）
             *    第二个参数：第二个单元格的行数（从0开始）
             *    第三个参数：第一个单元格的列数（从0开始）
             *    第四个参数：第二个单元格的列数（从0开始）
             */
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(range);
            return workbook;
        }
        BigDecimal bigDecimal = null;
        MathContext mathContext = new MathContext(PRECISION, RoundingMode.HALF_UP);
        Class clazz = list.get(0).getClass();
        //解析xml文件，解析出导出列
        List entities = tool.parseXML(clazz);
        if ((entities == null) || (entities.size() == 0)) {
            entities = tool.parseAnnotation(clazz);
        }
        //创建HSSFWorkbook
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        int index = 0;
        SXSSFSheet sheet = null;
        Drawing patriarch = null;
        for (int i = 0; i < list.size(); ++i) {
            //当条数大于阈值时切换sheet
            if ((i % EXCEL_ROW_MAX_SIZE) == 0) {
                //创建HSSFSheet
                sheet = workbook.createSheet(sheetName+index);
                //创建HSSFRow
                SXSSFRow titleRow = sheet.createRow(0);
                titleRow.setHeightInPoints(25.0F);
                //创建标题行
                tool.createTitleRow(workbook, titleRow, entities, sheet);
                // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                patriarch = sheet.createDrawingPatriarch();
                index ++;
            }
            //切换sheet后需要自动计算数据在当前sheet的rowPosition
            int rowNum = (i + 1) - ((index-1) * EXCEL_ROW_MAX_SIZE);
            //创建行
            SXSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(23.0F);
            int colNum = 0;
            sheetHandler(workbook, mathContext, list, entities, patriarch, i, rowNum, row, colNum);
        }
        return workbook;
    }

    /**
     * 导出数据到excel
     *
     * @param list      List<? extends Object>
     * @param sheetName sheet名称
     * @return HSSFWorkbook
     * @throws Exception
     */
    public SXSSFWorkbook exportToExcel(List<? extends Object> list, String sheetName, Class clazz) throws Exception {
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File dir = new File(path.getAbsolutePath(), "poitmp/");
        if (!dir.exists()) {
            dir.mkdirs();
            TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
        }
        if (list == null || list.size() == 0) {
            //提示暂无内容
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            SXSSFSheet sheet = workbook.createSheet(sheetName);
            SXSSFRow row = sheet.createRow(0);
            // 设置字体
            Font headfont = workbook.createFont();
            headfont.setFontName("黑体");
            // 字体大小
            headfont.setFontHeightInPoints((short) 22);
            // 加粗
            headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            CellStyle headstyle = workbook.createCellStyle();
            headstyle.setFont(headfont);
            // 左右居中
            headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 上下居中
            headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            // 创建第一行
            SXSSFRow row0 = sheet.createRow(0);
            // 设置行高
            row0.setHeight((short) 900);
            // 创建第一列
            SXSSFCell cell0 = row0.createCell(0);
            cell0.setCellValue(new XSSFRichTextString("暂无数据！！！"));
            cell0.setCellStyle(headstyle);
            /**
             * 合并单元格
             *    第一个参数：第一个单元格的行数（从0开始）
             *    第二个参数：第二个单元格的行数（从0开始）
             *    第三个参数：第一个单元格的列数（从0开始）
             *    第四个参数：第二个单元格的列数（从0开始）
             */
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(range);
            return workbook;
        }
        BigDecimal bigDecimal = null;
        MathContext mathContext = new MathContext(PRECISION, RoundingMode.HALF_UP);
        if(null == clazz){
            clazz = list.get(0).getClass();
        }
        //解析xml文件，解析出导出列
        List entities = tool.parseXML(clazz);
        if ((entities == null) || (entities.size() == 0)) {
            entities = tool.parseAnnotation(clazz);
        }
        //创建HSSFWorkbook
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        int index = 0;
        SXSSFSheet sheet = null;
        Drawing patriarch = null;
        for (int i = 0; i < list.size(); ++i) {
            //当条数大于阈值时切换sheet
            if ((i % EXCEL_ROW_MAX_SIZE) == 0) {
                //创建HSSFSheet
                sheet = workbook.createSheet(sheetName+index);
                //创建HSSFRow
                SXSSFRow titleRow = sheet.createRow(0);
                titleRow.setHeightInPoints(25.0F);
                //创建标题行
                tool.createTitleRow(workbook, titleRow, entities, sheet);
                // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                patriarch = sheet.createDrawingPatriarch();
                index ++;
            }
            //切换sheet后需要自动计算数据在当前sheet的rowPosition
            int rowNum = (i + 1) - ((index-1) * EXCEL_ROW_MAX_SIZE);
            //创建行
            SXSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(23.0F);
            int colNum = 0;
            for (Iterator localIterator = entities.iterator(); localIterator.hasNext(); ) {
                Entity entity = (Entity) localIterator.next();
                String type = entity.getType();
                String name = entity.getNeme();
                //创建列
                SXSSFCell cell = row.createCell(colNum);
                Object obj = null;
                if (("boolean".equals(type)) || ("java.lang.Boolean".equals(type))) {
                    obj = MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnIsBooleanMethodName(name));
                } else {
                    obj = MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnGetMethodName(name));
                }
                //设置数据到列
                if (obj != null) {
                    if (type.endsWith("String")) {
                        //终端故障图片
                        if ("breakdownImg".equals(name)) {
                            String url = String.valueOf(obj);
                            //取一张图片写入excel
                            if (url.contains(",")) {
                                url = url.substring(0, url.indexOf(","));
                            }
                            inertImg(patriarch, workbook, row, (short) colNum, (rowNum - 1), url);
                        } else {
                            cell.setCellValue((String) obj);
                        }

                    } else if (("int".equalsIgnoreCase(type)) || ("java.lang.Integer".equals(type)) || ("Integer".equals(type))) {
                        cell.setCellValue((Integer) obj);
                    } else if (("double".equalsIgnoreCase(type)) || ("java.lang.Double".equals(type))) {
                        bigDecimal = new BigDecimal((Double) obj, mathContext);
                        cell.setCellValue(bigDecimal.doubleValue());
                    } else if (("boolean".equalsIgnoreCase(type)) || ("java.lang.Boolean".equals(type))) {
                        cell.setCellValue((Boolean) MethodToolUtil.executeMethod(list.get(i), MethodToolUtil.returnIsBooleanMethodName(name)));
                    } else if (("float".equalsIgnoreCase(type)) || ("java.lang.Float".equals(type))) {
                        bigDecimal = new BigDecimal((Float) obj, mathContext);
                        cell.setCellValue(bigDecimal.doubleValue());
                    } else if (("java.util.Date".equals(type)) || (type.endsWith("Date"))) {
                        cell.setCellValue((Date) obj);
                    } else if ("java.util.Calendar".equals(type)) {
                        cell.setCellValue((Calendar) obj);
                    } else if (("char".equalsIgnoreCase(type)) || ("java.lang.Character".equals(type))) {
                        cell.setCellValue(obj.toString());
                    } else if (("long".equalsIgnoreCase(type)) || ("java.lang.Long".equals(type))) {
                        cell.setCellValue((Long) obj);
                    } else if (("short".equalsIgnoreCase(type)) || ("java.lang.Short".equals(type))) {
                        cell.setCellValue((Short) obj);
                    } else if ("java.math.BigDecimal".equals(type)) {
                        bigDecimal = (BigDecimal) obj;
                        bigDecimal = new BigDecimal(bigDecimal.doubleValue(), mathContext);
                        cell.setCellValue(bigDecimal.doubleValue());
                    } else {
                        throw new Exception("data type error!");
                    }
                }
                ++colNum;
            }
        }
        return workbook;
    }

    /**
     * 插入图片
     *
     * @param patriarch 顶级容器 HSSFPatriarch 一个sheet只能创建一个
     * @param workbook  工作簿
     * @param row       行对象
     * @param colNum    列（0开始）
     * @param rowNum    行（0开始）
     * @param url       图片地址
     * @throws IOException
     */
    private void inertImg(Drawing patriarch, SXSSFWorkbook workbook, SXSSFRow row,
                          short colNum, int rowNum, String url) throws IOException {
        if (StringUtils.isBlank(url)) {
            return;
        }
        if (url.contains(huaweiBaseUrl)) {
            String uri = url.substring(url.indexOf(huaweiBaseUrl) + huaweiBaseUrl.length());
            url = huaweiBaseUrl + URLEncoder.encode(uri, "utf-8");
        }
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        URL imgUrl = new URL(url);
        BufferedImage bufferImg = ImageIO.read(imgUrl);
        //原始宽度
        int width = bufferImg.getWidth();
        //原始高度
        int height = bufferImg.getHeight();
        // 一个12号字体的宽度为13,前面已设置了列的宽度为30*256，故这里的等比例高度计算如下
        height = (int) Math.round((height * (30 * 13) * 1.0 / width));
        // excel单元格高度是以点单位，1点=2像素; POI中Height的单位是1/20个点，故设置单元的等比例高度如下
        row.setHeight((short) (height / 2 * 20));
        ImageIO.write(bufferImg, "png", byteArrayOut);
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, colNum, rowNum, colNum, rowNum);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
    }
}

